AccountRouteConfig

V8 Message Definiton

METADATA

Attribute	Value
Topic	1800-client-config
MLink Token	ClientControl
Product	SRControl
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI	''	SR assigned accnt code
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm acronym
execBrkrCode	VARCHAR(16)	PRI	''	SR execBrkrCode SR Assigned
enabled	enum - YesNo		'None'	yesroute enabled
relationship	enum - RelationshipType		'None'
catDestFirmIMID	VARCHAR(16)		''	Destination Firm IMID supplied by FINRA fbo destination can be BD or Exchange
catDestDeptType	enum - CatDeptType		'None'	CAT destination department type
routingRule	enum - RoutingRule		'RoundRobin'
backupRouteCode	VARCHAR(65)		''	backup route code if above are unavailable
externParams	TINYTEXT		''	pass through params must be understood by end point handler usually tagvaluetagvaluetagvalue
comment	TINYTEXT		''	maintainer commments
modifiedBy	VARCHAR(24)		''	user who last modified this record
modifiedIn	enum - SysEnvironment		'None'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	timestamp of last modification
ClearingFlipList	JSON		'JSON_ARRAY()'
ExchControlList	JSON		'JSON_ARRAY()'
ExecBrkrAccntsList	JSON		'JSON_ARRAY()'
RoutingTableList	JSON		'JSON_ARRAY()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
clientFirm	2
execBrkrCode	3

JSON Block (ClearingFlipList)

Field	Type	Comment
exDest	enum - exDest	exDest eg CBOEOPT NMSOPT NMSSTK CMXFUT
clrFlipType	enum - FlipType	type of clearing corp delivery
clrFlipFirm	enum - clrFlipFirm	deliverTo clearing member eg OCC NSCC MPID or InstitutionID
clrFlipAccnt	enum - clrFlipAccnt	deliverTo client account eg OCC AID or a DVP FBO code
clrAgent	enum - clrAgent	deliverTo agent eg DVP Agent Bank ID
clrTaxID	enum - clrTaxID	deliverTo taxID eg DVP TaxID
clrBrkrMPID	enum - clrBrkrMPID	clearing broker equity clearing only exDest is an option exch exDestNMSSTK must exist in this list

JSON Block (ExchControlList)

Field	Type	Comment
exDest	enum - exDest	exchange eg NYSESTK CBOEOPT NMSOPT NMSSTK CMXFUT or custom eg INETNIGHTHAWK
disable	enum - YesNo

JSON Block (ExecBrkrAccntsList)

Field	Type	Comment
exchGroup	enum - ExchGroup	eg NMS CMX ICE EUREX NONEANY
secType	enum - SpdrSecType	eg Stock Future Option NONEANY
execBrkrAccnt	enum - execBrkrAccnt	exec broker account supplied by exec broker usually Account1
execBrkrClFirm	enum - execBrkrClFirm	exec broker clientfirm supplied by exec broker usually OnBehalfOfCompId115
execBrkrUserName	enum - execBrkrUserName	exec broker user name supplied by exec broker

JSON Block (RoutingTableList)

Field	Type	Comment
routingCode	enum - routingCode	routing session code is a RouteDefinitionpkeyroutingCode RouteDefinitionexecBrkrCode must match execBrkrCode
featureDisable	enum - FeatureDisable	used to disable certain route features before reaching the routingCoderouteDefinition

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRControl`.`MsgAccountRouteConfig` (
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned accnt code',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned client firm acronym',
    `execBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR execBrkrCode (SR Assigned)',
    `enabled` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'yes=route enabled',
    `relationship` ENUM('None','EXSMember','EXSRouter','EXSTech','Platform','SRConnect','Advisor') NOT NULL DEFAULT 'None',
    `catDestFirmIMID` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Destination Firm IMID (supplied by FINRA fbo destination; can be B/D or Exchange)',
    `catDestDeptType` ENUM('None','Exchange','Agency','ATS','DMA','Sponsored','Trading','Other') NOT NULL DEFAULT 'None' COMMENT 'CAT destination department type',
    `routingRule` ENUM('None','RoundRobin','InOrder') NOT NULL DEFAULT 'RoundRobin',
    `backupRouteCode` VARCHAR(65) NOT NULL DEFAULT '' COMMENT 'backup route code (if above are unavailable)',
    `externParams` TINYTEXT NOT NULL DEFAULT '' COMMENT 'pass through params (must be understood by end point handler) usually [tag=value;tag=value;tag=value]',
    `comment` TINYTEXT NOT NULL DEFAULT '' COMMENT 'maintainer commments',
    `modifiedBy` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'user who last modified this record',
    `modifiedIn` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'timestamp of last modification',
    `ClearingFlipList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ClearingFlipList)),
    `ExchControlList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ExchControlList)),
    `ExecBrkrAccntsList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ExecBrkrAccntsList)),
    `RoutingTableList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(RoutingTableList)),
    PRIMARY KEY USING HASH (`accnt`,`clientFirm`,`execBrkrCode`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `accnt`,
    `clientFirm`,
    `execBrkrCode`,
    `enabled`,
    `relationship`,
    `catDestFirmIMID`,
    `catDestDeptType`,
    `routingRule`,
    `backupRouteCode`,
    `externParams`,
    `comment`,
    `timestamp`,
    `ClearingFlipList`,
    `ExchControlList`,
    `ExecBrkrAccntsList`,
    `RoutingTableList`
FROM `SRControl`.`MsgAccountRouteConfig`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm'
  AND
    /* Replace with a VARCHAR(16) */ 
    `execBrkrCode` = 'Example_execBrkrCode';

Doc Columns Query

SELECT * FROM SRControl.doccolumns WHERE TABLE_NAME='AccountRouteConfig' ORDER BY ordinal_position ASC;